Imports System.Data
Imports System.Data.SqlClient
Imports CAPA_Entidad
Imports CAPA_Utilitarios
Public Class DocumentoAlmacenDetallePickeadorDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function registrarDocumentoAlmacenDetallePickeador(ByVal dadp As DocumentoAlmacenDetallePickeador) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "USP_RegistrarDocumentoAlmacenDetallePickeador"

            Dim comando As New SqlCommand(query, con)
            With comando
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@CIA", dadp.CIA)
                .Parameters.AddWithValue("@SUC", dadp.SUC_Codigo)
                .Parameters.AddWithValue("@TDOC", dadp.TDOC_Codigo)
                .Parameters.AddWithValue("@DCACOD", dadp.DCA_Codigo)
                .Parameters.AddWithValue("@DADPCOD", dadp.DADP_Codigo)
                .Parameters.AddWithValue("@DADCOD", dadp.DAD_Codigo)
                .Parameters.AddWithValue("@DAPCOD", dadp.DAP_Codigo)
                .Parameters.AddWithValue("@PICKCOD", dadp.PICK_Codigo)
                .Parameters.AddWithValue("@ALM", dadp.ALM_Codigo)
                .Parameters.AddWithValue("@DADPCANT", dadp.DADP_Cantidad)
                .Parameters.AddWithValue("@ACT", dadp.DADP_Activo)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function actualizarDocumentoAlmacenDetallePickeador(ByVal dadp As DocumentoAlmacenDetallePickeador) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "USP_ActualizarDocumentoAlmacenDetallePickeador"

            Dim comando As New SqlCommand(query, con)
            With comando
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@CIA", dadp.CIA)
                .Parameters.AddWithValue("@SUC", dadp.SUC_Codigo)
                .Parameters.AddWithValue("@TDOC", dadp.TDOC_Codigo)
                .Parameters.AddWithValue("@DCACOD", dadp.DCA_Codigo)
                .Parameters.AddWithValue("@DADPCOD", dadp.DADP_Codigo)
                .Parameters.AddWithValue("@DADCOD", dadp.DAD_Codigo)
                .Parameters.AddWithValue("@DAPCOD", dadp.DAP_Codigo)
                .Parameters.AddWithValue("@PICKCOD", dadp.PICK_Codigo)
                .Parameters.AddWithValue("@ALM", dadp.ALM_Codigo)
                .Parameters.AddWithValue("@DADPCANT", dadp.DADP_Cantidad)
                .Parameters.AddWithValue("@DADPCPICK", dadp.DADP_CantPick)
                .Parameters.AddWithValue("@DADPASIG", dadp.DADP_Asig_Act)
                .Parameters.AddWithValue("@FECINI", dadp.DADP_FechaInicio)
                .Parameters.AddWithValue("@FECFIN", dadp.DADP_FechaFin)
                .Parameters.AddWithValue("@ACT", dadp.DADP_Activo)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function obtenerListadoDocumentoAlmacenDetallePickeador(ByVal codCIA As String, ByVal codSUC As String, ByVal codTDOC As String, ByVal codDCA As String, ByVal codDAP As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()
        Try
            con.Open()
            Dim query As String = "SELECT * FROM VW_DocumentoAlmacenDetallePickeador " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCA_Codigo = @DCA AND DAP_Codigo = @DAP"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDOC", codTDOC)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DAP", codDAP)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function obtenerDocumentoAlmacenDetallePickeador(ByVal codCIA As String, ByVal codSUC As String, ByVal codTDOC As String, ByVal codDCA As String, ByVal codDADP As String, ByVal codDAD As String, ByVal codDAP As String) As DocumentoAlmacenDetallePickeador
        Dim dadp As New DocumentoAlmacenDetallePickeador("-1", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")
        Dim con As SqlConnection = conex.obtenerConexion()
        Try
            con.Open()
            Dim query As String = "SELECT * FROM VW_DocumentoAlmacenDetallePickeador " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCA_Codigo = @DCA AND DADP_Codigo = @DADP AND DAD_Codigo = @DAD AND DAP_Codigo = @DAP"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDOC", codTDOC)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DADP", codDADP)
                .Parameters.AddWithValue("@DAD", codDAD)
                .Parameters.AddWithValue("@DAP", codDAP)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                dadp = New DocumentoAlmacenDetallePickeador(ds.Tables(0).Rows(0)("CIA").ToString.Trim, ds.Tables(0).Rows(0)("SUC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TDOC_Codigo").ToString.Trim, _
                                                            ds.Tables(0).Rows(0)("DCA_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DADP_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DAD_Codigo").ToString.Trim, _
                                                            ds.Tables(0).Rows(0)("DAP_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("PICK_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("ALM_Codigo").ToString.Trim, _
                                                            ds.Tables(0).Rows(0)("DADP_Cantidad").ToString.Trim, ds.Tables(0).Rows(0)("DADP_FechaAsignacion").ToString.Trim, ds.Tables(0).Rows(0)("DADP_FechaInicio").ToString.Trim, _
                                                            ds.Tables(0).Rows(0)("DADP_FechaFin").ToString.Trim, ds.Tables(0).Rows(0)("DADP_CantPick").ToString.Trim, ds.Tables(0).Rows(0)("DADP_Asig_Act").ToString.Trim, _
                                                            ds.Tables(0).Rows(0)("DADP_Activo").ToString.Trim)
            End If
        Catch ex As Exception
            dadp = Nothing
        Finally
            con.Close()
        End Try

        Return dadp
    End Function

    Function contarPedidosPendientes(ByVal codPick As String) As Integer
        Dim numero As Integer = 0
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()
            Dim query As String = "SELECT distinct(dadp.DCA_Codigo) " & _
                                  "FROM documento_Almacen_detalle_pickeador dadp " & _
                                  "INNER JOIN Documento_Almacen_Detalle dad ON dad.CIA = dadp.CIA " & _
                                  "AND dad.SUC = dadp.SUC_Codigo AND dad.TDOC_Codigo = dadp.TDOC_Codigo " & _
                                  "AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Activo = '1' " & _
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad ON dcad.CIA_Almacen = dad.CIA " & _
                                  "AND dcad.SUC_Almacen = dad.SUC AND dcad.TDOC_Almacen = dad.TDOC_Codigo " & _
                                  "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DCAD_Activo = '1' " & _
                                  "INNER JOIN Documento_Cliente_Detalle dcd ON dcd.CIA = dcad.CIA_Cliente " & _
                                  "AND dcd.SUC = dcad.SUC_Cliente AND dcd.TDOC_Codigo = dcad.TDOC_Cliente " & _
                                  "AND dcd.DCI_Codigo = dcad.DCI_Codigo AND dcd.DCD_Activo = '1' " & _
                                  "INNER JOIN Documento_Cliente dc ON dc.CIA = dcd.CIA AND dc.SUC_Codigo = dcd.SUC " & _
                                  "AND dc.TDOC_Codigo = dcd.TDOC_Codigo AND dc.DCI_Codigo = dcd.DCI_Codigo " & _
                                  "AND (dc.DCI_Estado = '06' OR dc.DCI_Estado = '15') " & _
                                  "WHERE dadp.PICK_Codigo = @PICK " & _
                                  "AND dadp.TDOC_Codigo = 'PK' " & _
                                  "AND dadp.DADP_Activo = '1' " & _
                                  "AND dadp.DADP_FechaFin IS NULL"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@PICK", codPick)
            End With
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            numero = CInt(ds.Tables(0).Rows.Count)
        Catch ex As Exception
            numero = -1
        Finally
            con.Close()
        End Try

        Return numero
    End Function

    Function listarPedidosDetallePickeador(ByVal codPick As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT dadp.DCA_Codigo,dc.DCI_Codigo as Pedido, dap.DAP_Codigo, cli.CLI_Descripcion as Cliente, " & _
                                  "SUBSTRING(convert(varchar(20),dap.DAP_FechaAsig,103),1,10) AS Fecha, " & _
                                  "SUBSTRING(convert(varchar(20),dap.DAP_FechaAsig,113),12,6) AS Hora, " & _
                                  "COUNT(dadp.DADP_Codigo) AS Items, " & _
                                  "(CASE WHEN dc.DCI_Estado = '06' THEN 'Picking' " & _
                                  "WHEN dc.DCI_Estado = '15' THEN 'Asignacion' END) AS Estado " & _
                                  "FROM Documento_Almacen_Detalle_Pickeador dadp " & _
                                  "INNER JOIN Documento_Almacen_Detalle dad ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo " & _
                                  "AND dad.TDOC_Codigo = dadp.TDOC_Codigo AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo " & _
                                  "AND dad.DAD_Activo = '1' " & _
                                  "INNER JOIN Documento_Almacen_Pickeador dap ON dap.CIA = dadp.CIA AND dap.SUC = dadp.SUC_Codigo " & _
                                  "AND dap.TDOC_Codigo = dadp.TDOC_Codigo AND dap.DCA_Codigo = dadp.DCA_Codigo AND dap.DAP_Activo = '1' " & _
                                  "AND dap.DAP_Codigo = dadp.DAP_Codigo " & _
                                  "LEFT JOIN Documento_Cliente_Almacen_Detalle dcad ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC " & _
                                  "AND dcad.TDOC_Almacen = dad.TDOC_Codigo AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " & _
                                  "AND dcad.DCAD_Activo = '1' " & _
                                  "INNER JOIN Documento_Cliente dc ON dc.CIA = dcad.CIA_Cliente AND dc.SUC_Codigo = dcad.SUC_Cliente " & _
                                  "AND dc.TDOC_Codigo = dcad.TDOC_Cliente AND dc.DCI_Codigo = dcad.DCI_Codigo " & _
                                  "AND (dc.DCI_Estado = '06' OR DCI_Estado = '15') " & _
                                  "INNER JOIN cliente cli ON dc.Cli_Codigo = cli.CLI_Codigo " & _
                                  "WHERE dadp.CIA = '01' AND dadp.SUC_Codigo = '01' AND dadp.TDOC_Codigo = 'PK' " & _
                                  "AND dadp.PICK_Codigo = '" & codPick & "' AND dadp.DADP_Activo = '1' " & _
                                  "GROUP BY dadp.DCA_Codigo,dc.DCI_Codigo,dap.DAP_Codigo,cli.CLI_Descripcion,dap.DAP_FechaAsig, dc.DCI_Estado " & _
                                  "HAVING SUM(dadp.DADP_Cantidad) - SUM(dadp.DADP_CantPick) > 0"

            Dim adapter As New SqlDataAdapter(query, con)
            adapter.Fill(ds, "Pedidos")
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

End Class
